/**   
 * Copyright © 2019 eSunny Info. Tech Ltd. All rights reserved.
 * 
 * @Description:TODO(功能)
 * @className ProviderDaoImpl.java
 * @Package: com.erp.dao 
 * @author: yj   
 * @date: 2019年1月17日 上午9:59:11 
 */
package com.erp.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.erp.po.Providers;
import com.erp.util.Page;

/**
 * @className ProviderDaoImpl.java
 * @author yj
 * @Description:TODO(功能)
 * @Date 2019年1月17日
 */
public class ProviderDaoImpl extends BaseDao implements ProviderDao {

	/*
	 * ( 查询 ( 查询 Provider <供应商> 全部 )(分页))
	 * 
	 */
	@Override
	public ResultSet selectProvidersByPage(Connection conn, Page<Providers> page) throws SQLException {
		// String sql = "select * from providers  ORDER BY providerID  limit ?,? ";
		String sql = "select * from (select rownum rn,providers.* from providers where rownum <= ? ORDER BY providerID ) temp where temp.rn > ? ";
		// Object[] obj = { ((page.getCurrentPage() - 1) * page.getPageSize()), page.getPageSize() };	// mysql
		Object[] obj = { (page.getCurrentPage() * page.getPageSize()), ((page.getCurrentPage() - 1) * page.getPageSize()) };
		return super.selectData(conn, sql, obj);
	}

	/* (查询 ( 总记录数 ))
	 * 
	 */
	@Override
	public ResultSet selectProvidersByTotal(Connection conn) throws SQLException {
		String sql = "select count(*) from providers";
		return super.selectData(conn, sql, null);
	}
	
	
	/*
	 * (增加 ( 增加 供应商 ))
	 * 
	 */
	@Override
	public int insertProviders(Connection conn, Providers providers) throws SQLException {
		// String sql = "insert into providers(provider_name,provider_add,provider_tel,account,email) "
		//		+ " values(?,?,?,?,?) ";
		String sql = "insert into providers(providerID,provider_name,provider_add,provider_tel,account,email) "
				+ " values(PROVIDERS_SEQ.nextval,?,?,?,?,?) ";
		Object[] obj = { providers.getProviderName(), providers.getProviderAdd(), providers.getProviderTel(),
				providers.getAccount(), providers.getEmail() };
		return super.updateData(conn, sql, obj);
	}
	/* (查询 (是否有添加重名的商品))
	 * 
	 */
	@Override
	public ResultSet selectProvidersByInsert(Connection conn, Providers providers) throws SQLException {
		String sql = "select * from providers where provider_name = ? ";
		return super.selectData(conn, sql, providers.getProviderName());
	}

	

	/* (修改 (根据id 修改 供应商() ))
	 * 
	 */
	@Override
	public int updateProvidersById(Connection conn, Providers providers) throws SQLException {
		String sql = "update providers set provider_name = ? , provider_add = ? , provider_tel = ? , account = ? , email = ? "
				+ "  where  providerID = ? ";
		Object[] obj = { providers.getProviderName(), providers.getProviderAdd(), providers.getProviderTel(),
				providers.getAccount(), providers.getEmail() ,providers.getProviderId() };
		return super.updateData(conn, sql, obj);
	}

	/* (删除 ( 删除 供应商 ) -- 外键校验)
	 * 
	 */
	
	@Override
	public int deleteProvidersById(Connection conn, int providerId) throws SQLException {
		String sql = "delete from providers where providerID = ? ";
		return super.updateData(conn, sql, providerId);
	}

	
	/* (查询 Provider <供应商> 全部)
	 * 
	 */
	@Override
	public ResultSet selectProviders(Connection conn) throws SQLException {
		String sql = "select * from providers ORDER BY providerID";
		return super.selectData(conn, sql, null);
	}

	/* (根据id查询 Provider)
	 * 
	 */
	@Override
	public ResultSet selectProviderById(Connection conn, int id) throws SQLException {
		String sql = "select * from providers where providerID = ? ";
		return super.selectData(conn, sql, id);
	}

	/**
	 * 根据名称查询记录
	 */
	public ResultSet selectProviderByName(Connection conn, String providName) throws SQLException {
		String sql = "select * from providers where provider_name = ? ";
		return super.selectData(conn, sql, providName);
	}

	/* 
	 * 根据条件查询 ( 根据条件查询 Provider <供应商> 全部 )(分页)
	 */
	@Override
	public ResultSet selectProvidersByCondition(Connection conn, Providers pro, Page<Providers> page)
			throws SQLException {
		ArrayList list = new ArrayList();
		String sql = "select * from (select rownum rn,providers.* from providers where rownum <= ? ";
		list.add(page.getCurrentPage() * page.getPageSize());
		if(pro.getProviderName() != null) {
			sql = sql.concat(" and provider_name like ? ");
			list.add("%"+pro.getProviderName()+"%");
		}
		sql = sql.concat(" ORDER BY providerID ) temp where temp.rn > ? ");
		list.add((page.getCurrentPage() - 1) * page.getPageSize());
		return super.selectData(conn, sql, list.toArray());
	}

	/* 
	 * 根据条件查询 ( 总记录数 )
	 */
	@Override
	public ResultSet selectConditionByTotal(Connection conn, Providers pro) throws SQLException {
		ArrayList list = new ArrayList();
		String sql = "select count(providerID) from providers where 1=1";
		if(pro.getProviderName() != null) {
			sql = sql.concat(" and provider_name like ? ");
			list.add("%"+pro.getProviderName()+"%");
		}
		return super.selectData(conn, sql, list.toArray());
	}


	
	
	
	
	
	
	
	
	
	

}
